/*
 * PKL 51 GUNUNG KIDUL
 * Subseksie DATA ENTRY + CAPI
 */
package pkl51.service.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import pkl51.database.DatabaseUtility;
import pkl51.entity.Ruta;
import pkl51.error.EntityException;
import pkl51.service.RutaDao;

/**
 *
 * @author Hindarwan
 */
public class RutaDaoImpl implements RutaDao{

    private Connection connection;
    private final String insertRuta = "INSERT INTO ruta VALUES (?,?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE B1R2 = ?, B1R3 = ?, B1R4 = ?, B1R5 = ?, B1R6 = ?, B1R8 = ?, nim_pengentri = ?";
    private final String updateRuta = "UPDATE ruta SET B1R2 = ?, B1R3 = ?, B1R4 = ?, B1R5 = ?, B1R6 = ?, B1R8 = ?, nim_pengentri = ? WHERE NKS = ?";
    private final String getRutaByNKS = "SELECT * FROM ruta WHERE NKS = ?";
    
    @Override
    public void insertRuta(Ruta ruta) throws EntityException {
        PreparedStatement statement = null;
        try {
            connection = DatabaseUtility.getConnection();
            connection.setAutoCommit(false);
            
            statement = connection.prepareStatement(insertRuta);
            
            statement.setString(1, ruta.getNKS());
            statement.setString(2, ruta.getB1R2());
            statement.setString(3, ruta.getB1R3());
            statement.setString(4, ruta.getB1R4());
            statement.setString(5, ruta.getB1R5());
            statement.setString(6, ruta.getB1R6());
            statement.setString(7, ruta.getB1R8());
            statement.setString(8, ruta.getNim_pengentri());
            statement.setString(9, ruta.getB1R2());
            statement.setString(10, ruta.getB1R3());
            statement.setString(11, ruta.getB1R4());
            statement.setString(12, ruta.getB1R5());
            statement.setString(13, ruta.getB1R6());
            statement.setString(14, ruta.getB1R8());
            statement.setString(15, ruta.getNim_pengentri());
            
            statement.executeUpdate();
            
            connection.commit();
            
        } catch (SQLException ex) {
            try {
                connection.rollback();
            } catch (SQLException ex1) {
                Logger.getLogger(RutaDaoImpl.class.getName()).log(Level.SEVERE, null, ex1);
            }
            Logger.getLogger(RutaDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
        } finally{
            try {
                connection.setAutoCommit(true);
            } catch (SQLException ex) {
                Logger.getLogger(RutaDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
            }
            if(statement!=null){
                try {
                    statement.close();
                } catch (SQLException ex) {
                    Logger.getLogger(RutaDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
    }

    @Override
    public void updateRuta(Ruta ruta) throws EntityException {
        PreparedStatement statement = null;
        try {
            connection = DatabaseUtility.getConnection();
            connection.setAutoCommit(false);
            statement = connection.prepareStatement(updateRuta);
            
            statement.setString(1, ruta.getB1R2());
            statement.setString(2, ruta.getB1R3());
            statement.setString(3, ruta.getB1R4());
            statement.setString(4, ruta.getB1R5());
            statement.setString(5, ruta.getB1R6());
            statement.setString(6, ruta.getB1R8());
            statement.setString(7, ruta.getNim_pengentri());
            statement.setString(8, ruta.getNKS());
            
            statement.executeUpdate();
            
            connection.commit();
        } catch (SQLException ex) {
            try {
                connection.rollback();
            } catch (SQLException ex1) {
                Logger.getLogger(RutaDaoImpl.class.getName()).log(Level.SEVERE, null, ex1);
            }
            Logger.getLogger(RutaDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
        }finally{
            try {
                connection.setAutoCommit(true);
            } catch (SQLException ex) {
                Logger.getLogger(RutaDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
            }
            if(statement!=null){
                try {
                    statement.close();
                } catch (SQLException ex) {
                    Logger.getLogger(RutaDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }    
    }

    @Override
    public Ruta getRutaByNKS(String NKS) throws EntityException {
        PreparedStatement statement = null;
        try {
            connection = DatabaseUtility.getConnection();            
            connection.setAutoCommit(false);
            statement = connection.prepareStatement(getRutaByNKS);
            statement.setString(1, NKS);
            
            ResultSet resultSet = statement.executeQuery();
            
            Ruta ruta = null;
            if (resultSet.next()) {
                ruta = new Ruta();
                ruta.setNKS(resultSet.getString("NKS"));
                ruta.setB1R2(resultSet.getString("B1R2"));
                ruta.setB1R3(resultSet.getString("B1R3"));
                ruta.setB1R4(resultSet.getString("B1R4"));
                ruta.setB1R5(resultSet.getString("B1R5"));
                ruta.setB1R6(resultSet.getString("B1R6"));
                ruta.setB1R8(resultSet.getString("B1R8"));
                ruta.setNim_pengentri(resultSet.getString("nim_pengentri"));
            } else {
                throw new EntityException("ruta tidak ditemukan");
            }
            
            connection.commit();
            
            return ruta;
        } catch (SQLException ex) {
            try {
                connection.setAutoCommit(true);
            } catch (SQLException ex1) {
                Logger.getLogger(RutaDaoImpl.class.getName()).log(Level.SEVERE, null, ex1);
            }
            Logger.getLogger(RutaDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
            throw new EntityException(ex.getMessage());
        } finally{
            try {
                connection.setAutoCommit(true);
            } catch (SQLException ex) {
                Logger.getLogger(RutaDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
            }
            if(statement!=null){
                try {
                    statement.close();
                } catch (SQLException ex) {
                    Logger.getLogger(RutaDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        
    }
    
}
